package com.example.exercise.MysqlUtil;

import com.example.exercise.Base.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassOpt {

    private static Connection conn=null;
    private static PreparedStatement ps=null;//操作整合sql语句的对象
    private static ResultSet rs=null;//查询结果的集合

    //DBService 对象
    public static DBService dbService=null;

    public  static  List getAllClass(){
        //结果存放集合
        List<String> list=new ArrayList<String>();
        //MySQL 语句
        String sql="select 班级 from classroom;";

        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            list.add(rs.getString("班级"));
                       }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Mysqlconnect.closeAll(conn,ps,rs);//关闭相关操作
        return list;
    }

    public  static  int ChangeClass(String Class1,String Class2){
        int result=-1;

        //获取链接数据库对象
        try {
            conn= Mysqlconnect.get().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        String sql="update classroom set 班级 = ? where 班级 = ?";
        try {
            boolean closed=conn.isClosed();
            if(conn!=null&&(!closed)){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                ps.setString(1,Class2);//第一个参数state 一定要和上面SQL语句字段顺序一致
                ps.setString(2,Class1);//第一个参数state 一定要和上面SQL语句字段顺序一致
                ps.executeUpdate();
                result=1;//返回1 执行成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public static int delClass(String Class){
        int result=-1;

        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        //MySQL 语句
        String sql="delete from classroom where 班级= ?";
        try {
            boolean closed=conn.isClosed();
            if((conn!=null)&&(!closed)){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                ps.setString(1, Class);
                ps.executeUpdate();
                result=1;//返回1 执行成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public static int delWholeClass(String Class){
        int result=-1;

        //获取链接数据库对象
        conn= Mysqlconnect.get().getConnection();
        //MySQL 语句
        String sql="delete from card where CLASS= ?";
        try {
            boolean closed=conn.isClosed();
            if((conn!=null)&&(!closed)){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                ps.setString(1, Class);
                ps.executeUpdate();
                result=1;//返回1 执行成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }

    public static int insertClass(String Class){
        int result=-1;
        if(Class!=null){
            //获取链接数据库对象
            conn= Mysqlconnect.get().getConnection();
            //MySQL 语句
            String sql="INSERT INTO classroom (班级) VALUES (?)";
            try {
                if((conn!=null)&&(!conn.isClosed())){
                    ps= (PreparedStatement) conn.prepareStatement(sql);
                    ps.setString(1,Class);//第三个参数 content 规则同上
                    result=ps.executeUpdate();//返回1 执行成功
                }


            } catch (SQLException e) {
                System.out.println("111111111111111111113");
                e.printStackTrace();
            }
        }
        Mysqlconnect.closeAll(conn,ps);//关闭相关操作
        return result;
    }


}
